package com.rx.mgt.util;

import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.rx.mgt.domain.UserInfoExt;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.http.MediaType;

import javax.print.attribute.standard.MediaTray;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;


public class ExcelUtil {

	public static String NO_DEFINE = "no_define";//未定义的字段
	public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式
	public static int DEFAULT_COLOUMN_WIDTH = 17;
	private static void createCellAndValue(XSSFSheet sheet,XSSFRow row, int position, String cellValue){
		int num = row.getRowNum();
		XSSFCell cell = row.getCell(position)==null?row.createCell(position):row.getCell(position);
		if(num>=3){
			XSSFCell cellc = sheet.getRow(num-1).getCell(position);
			cell.setCellStyle(cellc.getCellStyle());
		}
		cell.setCellType(Cell.CELL_TYPE_STRING);
		cell.setCellValue(cellValue);
	}


	/**
	 * 将excel内容读取,转换成List
	 * @param in
	 * @return
	 */
	public static Map<String,Object> ExcelToMap(InputStream in){
		List<Map<String,String>> dataList = new ArrayList<Map<String,String>>();
		XSSFWorkbook wb = null;
		try{
			//构建workbook对象
			wb = new XSSFWorkbook(in);
			//获取第一张Sheet表
			XSSFSheet sheet = wb.getSheetAt(0);
			//遍历excel表格，获取内容
			for(int i = sheet.getFirstRowNum() + 2  ; i <= sheet.getLastRowNum()-2; i ++){
				XSSFRow  row = sheet.getRow(i);
				if(row==null){
				    continue;
				}
				HashMap<String, String> map = new HashMap<String, String>();
				XSSFCell sx = row.getCell(0);
				if(sx==null){
					continue;
				}
				sx.setCellType(Cell.CELL_TYPE_STRING);
				row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
				row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
				row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
				row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
				row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
				row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
				row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
				row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
				row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
				row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
				row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
				
				map.put("name", row.getCell(0).getStringCellValue());
				map.put("sex", row.getCell(1).getStringCellValue());
				map.put("user_name", row.getCell(2).getStringCellValue());
				map.put("password", row.getCell(3).getStringCellValue());
				map.put("telephnoe", row.getCell(4).getStringCellValue());
				map.put("status", row.getCell(5).getStringCellValue());
				map.put("address", row.getCell(6).getStringCellValue());
				map.put("remark", row.getCell(7).getStringCellValue());
				map.put("max_mail_box_size", StringUtils.isBlank(row.getCell(8).getStringCellValue())?"0":row.getCell(10).getStringCellValue());
				map.put("max_message_size", StringUtils.isBlank(row.getCell(9).getStringCellValue())?"0":row.getCell(10).getStringCellValue());
				map.put("gjjAccount", row.getCell(10).getStringCellValue());
				dataList.add(map);
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		HashMap<String, Object> resultMap = new HashMap<String,Object>();
		resultMap.put("userList", dataList);
		return resultMap;
	}

	public static void exportExcelX(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
		if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
		// 声明一个工作薄
		XSSFWorkbook workbook = new XSSFWorkbook();//缓存
		//workbook.setCompressTempFiles(true);
		//表头样式
		CellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		Font titleFont = workbook.createFont();
		titleFont.setFontHeightInPoints((short) 20);
		titleFont.setBoldweight((short) 700);
		titleStyle.setFont(titleFont);
		// 列头样式
		CellStyle headerStyle = workbook.createCellStyle();
		headerStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		headerStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		Font headerFont = workbook.createFont();
		headerFont.setFontHeightInPoints((short) 12);
		headerStyle.setFont(headerFont);
		// 单元格样式
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFont(headerFont);
		cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setFillBackgroundColor((short)16);
		Font cellFont = workbook.createFont();
		cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
		cellFont.setBoldweight((short)12);
		cellFont.setFontHeightInPoints((short)12);

		cellStyle.setFont(cellFont);
		// 生成一个(带标题)表格
		XSSFSheet sheet = workbook.createSheet();
		//设置列宽
		int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
		int[] arrColWidth = new int[headMap.size()];
		// 产生表格标题行,以及设置列宽
		String[] properties = new String[headMap.size()];
		String[] headers = new String[headMap.size()];
		int ii = 0;
		for (Iterator<String> iter = headMap.keySet().iterator(); iter
				.hasNext();) {
			String fieldName = iter.next();

			properties[ii] = fieldName;
			headers[ii] = headMap.get(fieldName);

			int bytes = fieldName.getBytes().length;
			arrColWidth[ii] =  bytes < minBytes ? minBytes : bytes;
			sheet.setColumnWidth(ii,arrColWidth[ii]*256);
			ii++;
		}
		// 遍历集合数据，产生数据行
		int rowIndex = 0;
		for (Object obj : jsonArray) {
			if(rowIndex == 65535 || rowIndex == 0){
				if ( rowIndex != 0 ) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示

				XSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
				titleRow.createCell(0).setCellValue(title);
				titleRow.getCell(0).setCellStyle(titleStyle);
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));

				XSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
				for(int i=0;i<headers.length;i++)
				{
					headerRow.createCell(i).setCellValue(headers[i]);
					headerRow.getCell(i).setCellStyle(headerStyle);

				}
				rowIndex = 2;//数据内容从 rowIndex=2开始
			}
			JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
			XSSFRow dataRow = sheet.createRow(rowIndex);
			for (int i = 0; i < properties.length; i++)
			{
				XSSFCell newCell = dataRow.createCell(i);

				Object o =  jo.get(properties[i]);
				String  cellValue ="";
				if(o!=null)
					cellValue= o.toString();
//				if(o==null) cellValue = "";
//				else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
//				else if(o instanceof Float || o instanceof Double)
//					cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
//				else

				newCell.setCellValue(cellValue);
				newCell.setCellStyle(cellStyle);
			}
			rowIndex++;
		}
		// 自动调整宽度
        /*for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }*/
		try {
			workbook.write(out);
			workbook.close();

		} catch (IOException e) {
			e.printStackTrace();
		}
	}


	public static void downloadExcelFile(String title,Map<String,String> headMap,JSONArray ja,HttpServletResponse response){
		try {
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			ExcelUtil.exportExcelX(title,headMap,ja,null,0,os);
			byte[] content = os.toByteArray();
			InputStream is = new ByteArrayInputStream(content);
			// 设置response参数，可以打开下载页面
			response.reset();

			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
			response.setHeader("Content-Disposition", "attachment;filename="+ new String((title + ".xlsx").getBytes(), "iso-8859-1"));
			response.setContentLength(content.length);
			ServletOutputStream outputStream = response.getOutputStream();
			BufferedInputStream bis = new BufferedInputStream(is);
			BufferedOutputStream bos = new BufferedOutputStream(outputStream);
			byte[] buff = new byte[8192];
			int bytesRead;
			while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
				bos.write(buff, 0, bytesRead);

			}
			bis.close();
			bos.close();
			outputStream.flush();
			outputStream.close();
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static void createUTPExcel(String filename,String filePath,String[] titile, List<UserInfoExt> list,HttpServletResponse response){

		File mobanFile = new File(filePath);
		XSSFWorkbook wb =null;
		FileInputStream in = null;
		//构建workbook对象
		try {
			in = new FileInputStream(mobanFile);
			wb = new XSSFWorkbook(in);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		//获取第一张Sheet表
		XSSFSheet sheet = wb.getSheetAt(0);
		for(int i=0;i<list.size();i++){
			XSSFRow xrow = i==0?sheet.getRow(i+2):sheet.createRow(i+2);
			createCellAndValue(sheet,xrow,0,list.get(i).getName());
			createCellAndValue(sheet,xrow,1,list.get(i).getSex());
			createCellAndValue(sheet,xrow,2,list.get(i).getUser_name());
			createCellAndValue(sheet,xrow,3,list.get(i).getPassword());
			createCellAndValue(sheet,xrow,4,list.get(i).getTelephone());
			createCellAndValue(sheet,xrow,5,list.get(i).getStatus());
			createCellAndValue(sheet,xrow,6,list.get(i).getAddress());
			createCellAndValue(sheet,xrow,7,list.get(i).getRemark());
			createCellAndValue(sheet,xrow,8,String.valueOf(list.get(i).getMax_mail_box_size()==null?"":list.get(i).getMax_mail_box_size()));
			createCellAndValue(sheet,xrow,9,String.valueOf(list.get(i).getMax_message_size()==null?"":list.get(i).getMax_message_size()));
			createCellAndValue(sheet,xrow,10,list.get(i).getGjjAccount());
		}
		try {
			OutputStream outputStream = response.getOutputStream();
			response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
			response.setHeader("Content-Disposition","attachment;filename="+filename);
			response.setContentType("utf-8");
			wb.write(outputStream);
			outputStream.close();
		}catch (Exception e){
			e.printStackTrace();
		}


	}


}
